﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Web.UI.WebControls;

using Jumpton.SFA.Entity;
using Jumpton.Persistent;
using System.Data;
using Jumpton.Data;
using Jumpton.SFA.Server.Entity;

namespace Jumpton.SFA.Server.BusinessRule
{
    public class PromotionManager:BusinessLogic
    {

        public PromotionFormula Load(int ID)
        {

            return (PromotionFormula)ObjectRow.Load(typeof(PromotionFormula), string.Format("ID={0}", ID));
        }



        public ObjectTable LoadFormulasByDomainID(decimal domainId, string sb)
        {
            return new ObjectTable(typeof(PromotionFormula), string.Format("DOMAIN_ID={0} {1} ORDER BY ACTIVE DESC", domainId, sb));
        }



        public bool Save(PromotionFormula pf)
        {
            if(pf != null)
            {

                pf.Save();
                return true;
            }
            else
                return false;
        }

        public static bool AssignPromotionProduct(string productids, int pfid, decimal useid, decimal domainid)
        {
            return SqlData.ExecuteSP("sp_AssignPromotionProduct", productids, pfid, useid, domainid)>0?true :false;
        }

        public static DataSet GetSelectProducts(int PFID, decimal domainid)
        {
            string sql = @"SELECT P.*,C.NAME AS CategoryName,M.NAME AS ManuName,B.NAME AS BrandName,
CASE WHEN (PFD.PSID IS NOT NULL)
THEN 'true' ELSE 'false' END AS SELECTED
 FROM PRODUCTS P 
 LEFT JOIN  PromotionFormulaDetail PFD ON PFD.PSID=P.ID AND PFD.PFID={0}
 LEFT JOIN CATEGORIES C ON C.CODE=P.CATEGORY_CODE AND C.DOMAIN_ID=P.DOMAIN_ID 
 LEFT JOIN BRANDS B ON P.BRAND_CODE=B.CODE AND B.DOMAIN_ID=C.DOMAIN_ID 
 LEFT JOIN MANUFACTURERS M ON P.MANUFACTURER_CODE=M.CODE AND M.DOMAIN_ID=B.DOMAIN_ID 
 
 WHERE  P.DOMAIN_ID={1}  ORDER BY SELECTED DESC,SEQUENCE ASC";

            sql = string.Format(sql,PFID, domainid);
            return SqlData.OpenSql(sql);
        }

        /// <summary>
        /// 分页提取相关数据
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="strGetFields"></param>
        /// <returns></returns>
//        public static DataSet GetByPage(decimal domainId, decimal start, decimal end, string where)
//        {

//            string sql = @"WITH TEMPPHOTOS AS              
//                            (select row_number() over(order by V.START_TIME desc) as row, P.ID,P.PHOTO_PATH,P.PHOTO_TYPE_ID,P.USER_ID,V.START_TIME,C.NAME,C.CUST_CLASS_CODE FROM PHOTO P 
//                            LEFT JOIN VISITS V ON V.ID=P.VISIT_ID
//                            LEFT JOIN CUSTOMERS C ON C.ID=V.CUSTOMER_ID
//                            WHERE C.DOMAIN_ID={0} {1})
//                            SELECT * from TEMPPHOTOS WHERE ROW BETWEEN {2} AND {3}";

//            sql = string.Format(sql, domainId, where, start, end);
//            return SqlData.OpenSql(string.Format(sql, start, end));
//        }

     
        public static DataSet GetPromationFormulaAndDetail(string userid, string domainid)
        {
            string sql = @"SELECT * FROM PromotionFormula WHERE ID IN(
SELECT DISTINCT PFD.PFID
FROM  CUSTOMER_PROD_LIST_ITEMS CP
INNER JOIN CUSTOMERS C ON C.customer_product_list_id= CP.customer_product_list_id
INNER JOIN  ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID  AND C.VALID=RC.ACTIVE
LEFT  JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
INNER JOIN dbo.PromotionFormulaDetail PFD ON PFD.PSID=P.ID
WHERE RC.ROUTE_NUMBER=0 AND RC.USER_ID={0} AND C.VALID = '1'
AND C.DOMAIN_ID={1}); ";
            sql = string.Format(sql, userid, domainid);

            sql += @"SELECT P.ID,PFD.PFID,PFD.PSID,PFD.PTID 
FROM  CUSTOMER_PROD_LIST_ITEMS CP
INNER JOIN CUSTOMERS C ON C.customer_product_list_id= CP.customer_product_list_id
INNER JOIN  ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID  AND C.VALID=RC.ACTIVE
LEFT  JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
INNER JOIN dbo.PromotionFormulaDetail PFD ON PFD.PSID=P.ID
WHERE RC.ROUTE_NUMBER=0 AND RC.USER_ID={0} AND C.VALID = '1'
AND C.DOMAIN_ID={1} GROUP BY P.ID,PFD.PFID,PFD.PSID,PFD.PTID  ORDER BY P.ID; ";

            sql = string.Format(sql, userid, domainid);
            return SqlData.OpenSql(sql);
        }

        /// <summary>
        /// 分销数据查看
        /// </summary>
        /// <param name="domainID"></param>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetPromationTable(string domainID, string startdate, string enddate, string where)
        {

            string sql = @"SELECT  C.CODE AS CCODE,C.NAME AS CNAME,P.CODE AS PCODE,P.DESCRIPTION AS PRODUCTNAME
,isnull(KC.VALUE,0) AS KCVALUE,isnull(PM.VALUE,0) AS PMVALUE,dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar)) as date
FROM VISITS V
inner join CUSTOMERS C ON C.ID=V.CUSTOMER_ID  AND C.DOMAIN_ID='{0}' AND dbo.GetDateTimeByBigint(V.LAST_UPDATE_TIME) between '{1} 00:00:00' and '{2} 23:59:59'
inner join CUSTOMER_PROD_LIST_ITEMS CP ON C.customer_product_list_id= CP.customer_product_list_id 
inner JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
LEFT JOIN CPR_HJKC KC  ON P.ID=KC.PRODUCTS_ID AND C.ID=KC.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar))
LEFT JOIN CPR_PM PM ON PM.CUSTOMERS_ID=KC.CUSTOMERS_ID AND PM.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=PM.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(PM.CREATE_TIME as varchar))
 WHERE 1=1 AND V.STATUS=2 {3}
order by V.LAST_UPDATE_TIME desc,c.id,P.sequence
";


            sql = @"SELECT DISTINCT C.NAME AS CUSTOMERNAME,F.NAME AS FORMNAME, t1.PRODUCTS_ID,P.description AS PRODUCTNAME,t1.VALUE AS KCVALUE,t2.VALUE AS PMVALUE,dbo.GetDateStringByBigint(V.START_TIME) AS VTIME FROM
(
select KC.VISITID,KC.CUSTOMERS_ID,KC.PRODUCTS_ID,KC.VALUE,RC.USER_ID from CPR_KC_LAST_TIME  KC inner join ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=KC.CUSTOMERS_ID 
where kc.DOMAIN_ID={0}  AND RC.ROUTE_NUMBER=0
) t1,
(
select PM.CUSTOMERS_ID,PM.PRODUCTS_ID,PM.VALUE from CPR_KC_LAST_TIME  PM inner join ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=PM.CUSTOMERS_ID
where pm.DOMAIN_ID={1} AND  RC.ROUTE_NUMBER=0) t2,VISITS V,PRODUCTS P,CUSTOMERS C,FORMS F
where t1.visitid=V.ID AND t1.CUSTOMERS_ID=t2.CUSTOMERS_ID AND t1.PRODUCTS_ID=t2.PRODUCTS_ID AND V.CUSTOMER_ID=T1.CUSTOMERS_ID AND V.USER_ID=t1.USER_ID AND P.ID=T1.PRODUCTS_ID AND C.ID=V.CUSTOMER_ID AND F.CODE=P.FORM_CODE

AND dbo.GetDateTimeByBigint(V.LAST_UPDATE_TIME) between '{2} 00:00:00' and '{3} 23:59:59' AND V.STATUS=2 {4}
";
            sql = string.Format(sql, domainID,domainID,startdate,enddate, where);

            return SqlData.OpenSql(sql).Tables[0];
        }

    }
}
